﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DatabaseAccess
{
    public class SqlIt
    {
        public SqlIt() { }

        //获取指定表中的所有数据
        //select all from the table you want
        public DataSet SelectAll(String tableName, String field = "*")
        {
            using (DBAccess dba = new DBAccess())
            {
                return dba.ExecuteDataSet("select " + field + " from " + tableName);
            }
        }

        //获取制定表中符合条件的数据
        //condition是条件，field是要的字段名
        //整合成sql语句就是select field  from  tableName  where condition
        //比如 select UserName,Salt from UserInfo where MailBox='xxx@xxx.xxx'
        //一下的方法都类似
        //select a special item from the table you want
        public DataSet SpecialItem(String tableName, String condition, String field = "*")
        {
            using (DBAccess dba = new DBAccess())
            {
                return dba.ExecuteDataSet("select " + field + " from " + tableName + " where " + condition);
            }
        }

        //select a single item
        public Object SingleSpecialItem(String tableName, String condition, String field)
        {
            using (DBAccess dba = new DBAccess())
            {
                return dba.ExecuteScalar("select " + field + " from " + tableName + " where " + condition);
            }
        }

        //add special items
        //注意值要带''单引号,此方法专门用于只填一个字段的操作
        //多个字段请用AddMess
        public void SpecialAdd(String tableName, String field, String value)
        {
            using (DBAccess dba = new DBAccess())
            {
                dba.ExecuteNonQuery("insert into " + tableName + " (" + field + ") values ('" + value + "')");
            }
        }

        public void SpecialUpdate(String tableName, String change, String condition)
        {
            using (DBAccess dba = new DBAccess())
            {
                dba.ExecuteNonQuery("update "+tableName+" set "+change+" where " +condition);
            }
        }

        //Dictionary类使用时 对象 info["字段名"]="值";
        //add a mess
        public void AddMess(string table, Dictionary<string, string> info)
        {
            StringBuilder sb = new StringBuilder("insert into ");
            sb.Append(table).Append(" (");
            foreach (string col in info.Keys)
            {
                sb.Append("\"").Append(col).Append("\"").Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(") ");
            sb.Append("values(");
            foreach (string val in info.Values)
            {
                sb.Append("'").Append(val).Append("'").Append(",");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(") ");

            using (DBAccess dba = new DBAccess())
            {
                dba.ExecuteNonQuery(sb.ToString());
            }
        }

        //modify a mess
        public void UpdateMess(string table, Dictionary<string, string> info, string constraint)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update ").Append(table).Append(" set ");
            foreach (var en in info)
            {
                sb.Append('"').Append(en.Key).Append("\"='").Append(en.Value).Append("',");
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(" where ").Append(constraint);

            using (DBAccess dba = new DBAccess())
            {
                dba.ExecuteNonQuery(sb.ToString());
            }
        }

        //返回符合条件的结果集行数
        public int SpecialItemCount(String tableName, String condition)
        {
            return SpecialItem(tableName, condition).Tables[0].Rows.Count;
        }

        //判断表中是否有已存在符合条件的内容
        public bool IsExist(String tableName, String condition)
        {
            return SpecialItemCount(tableName, condition) > 0;
        }
        //删除表中符合条件的行
        public void SpecialDelete(String tableName,String condition)
        {
            using (DBAccess dba = new DBAccess())
            {
                dba.ExecuteNonQuery("delete from " + tableName + " where " +condition);
            }
        } 
    }
}
